'use strict';

const BaseService = require('./base');

class OrderService extends BaseService {
  get _model() {
    return 'Order';
  }

  async findOne({ id, houseId, status, uid, startTime, endTime, orderStatus, isSimple = false }) {
    const { app } = this;
    const where = this.formatWhere({ exact: { id, houseId, status, uid }, inOp: { status: orderStatus }, ...this.rangeWhere({ startTime, endTime }) });
    let include = [];
    if (!isSimple) {
      include = [
        {
          model: app.model.User,
          attributes: {
            exclude: app.exclude.concat([ 'password' ]),
          },
          include: [
            {
              model: app.model.UserInfo,
              attributes: {
                exclude: app.exclude,
              },
              include: [
                {
                  model: app.model.Area,
                  attributes: [ 'name' ],
                },
              ],
            },
          ],
        },
        {
          model: app.model.House,
          attributes: {
            exclude: app.exclude,
          },
          include: [
            {
              model: app.model.Area,
              attributes: [ 'name' ],
            },
            {
              model: app.model.HouseInfo,
              attributes: {
                exclude: app.exclude,
              },
            },
            {
              model: app.model.User,
              as: 'Owner',
              attributes: {
                exclude: app.exclude,
              },
              include: [
                {
                  model: app.model.UserInfo,
                  attributes: {
                    exclude: app.exclude,
                  },
                  include: [
                    {
                      model: app.model.Area,
                      attributes: [ 'name' ],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ];
    }
    return this._findOne({ where, include });
  }

  async findRange({ startTime, endTime, status, houseId }) {
    const where = this.rangeWhere({ startTime, endTime, status, houseId });
    return this._findOne({ where });
  }

  async create({ startTime, endTime, phoneNum, persons, amount, status, uid, houseId }) {
    return this._create({
      startTime, endTime, phoneNum, persons, amount, status, uid, houseId,
    });
  }

  async update({ status, id }) {
    const where = { id };
    const body = { status };
    return this._update({ body, where });
  }

  async destory(id) {
    const where = { id };
    return this._destory({ where });
  }

  async findList({ status, uid, oid, houseId, prop, sort, pageIndex, pageSize, createdAt }) {
    const { app } = this;
    const where = this.formatWhere({ exact: { status, uid, houseId }, range: { createdAt } });
    const whereHouse = this.formatWhere({ exact: { oid } });
    const order = this.formatOrder(prop, sort);
    const include = [
      {
        model: app.model.House,
        where: whereHouse,
        attributes: [ 'name' ],
        include: [
          {
            model: app.model.User,
            as: 'Owner',
            attributes: [ 'nickName' ],
          },
          {
            model: app.model.HouseInfo,
            attributes: [ 'pictures' ],
          },
        ],
      },
      {
        model: app.model.User,
        attributes: [ 'nickName' ],
      },
    ];
    return this._findList({ pageIndex, pageSize, where, order, include });
  }

  async statisticalIncome({ startTime, uid }) {
    if (!startTime) {
      startTime = new Date().getFullYear();
    }
    const sql = `SELECT 
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "1" THEN \`order\`.\`amount\` ELSE 0 END) AS "1", 
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "2" THEN \`order\`.\`amount\` ELSE 0 END) AS "2",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "3" THEN \`order\`.\`amount\` ELSE 0 END) AS "3",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "4" THEN \`order\`.\`amount\` ELSE 0 END) AS "4",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "5" THEN \`order\`.\`amount\` ELSE 0 END) AS "5",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "6" THEN \`order\`.\`amount\` ELSE 0 END) AS "6", 
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "7" THEN \`order\`.\`amount\` ELSE 0 END) AS "7",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "8" THEN \`order\`.\`amount\` ELSE 0 END) AS "8",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "9" THEN \`order\`.\`amount\` ELSE 0 END) AS "9",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "10" THEN \`order\`.\`amount\` ELSE 0 END) AS "10",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "11" THEN \`order\`.\`amount\` ELSE 0 END) AS "11",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "12" THEN \`order\`.\`amount\` ELSE 0 END) AS "12"
                    FROM (SELECT \`order\`.\`amount\`,\`order\`.\`startTime\` FROM \`order\` LEFT JOIN \`house\` ON \`order\`.\`houseId\` = \`house\`.\`id\` WHERE \`house\`.\`oid\` = ${uid} AND \`order\`.\`status\` = 2 ) AS \`order\` 
                    WHERE YEAR(\`order\`.\`startTime\`) = ${startTime}`;
    return await this.querySelect(sql);
  }

  async statisticalIncomeAll({ startTime }) {
    if (!startTime) {
      startTime = new Date().getFullYear();
    }
    const sql = `SELECT 
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "1" THEN \`order\`.\`amount\` ELSE 0 END) AS "1", 
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "2" THEN \`order\`.\`amount\` ELSE 0 END) AS "2",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "3" THEN \`order\`.\`amount\` ELSE 0 END) AS "3",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "4" THEN \`order\`.\`amount\` ELSE 0 END) AS "4",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "5" THEN \`order\`.\`amount\` ELSE 0 END) AS "5",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "6" THEN \`order\`.\`amount\` ELSE 0 END) AS "6", 
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "7" THEN \`order\`.\`amount\` ELSE 0 END) AS "7",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "8" THEN \`order\`.\`amount\` ELSE 0 END) AS "8",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "9" THEN \`order\`.\`amount\` ELSE 0 END) AS "9",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "10" THEN \`order\`.\`amount\` ELSE 0 END) AS "10",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "11" THEN \`order\`.\`amount\` ELSE 0 END) AS "11",
                    SUM(CASE WHEN MONTH(\`order\`.\`startTime\`) = "12" THEN \`order\`.\`amount\` ELSE 0 END) AS "12"
                    FROM (SELECT \`order\`.\`amount\`,\`order\`.\`startTime\` FROM \`order\` LEFT JOIN \`house\` ON \`order\`.\`houseId\` = \`house\`.\`id\` WHERE \`order\`.\`status\` = 2 ) AS \`order\` 
                    WHERE YEAR(\`order\`.\`startTime\`) = ${startTime}`;
    return await this.querySelect(sql);
  }

  // 订单交易量
  async tradingVolume({ startTime, areaId }) {
    if (!startTime) {
      startTime = new Date().getFullYear();
    }
    let where = 'AND YEAR(`order`.`updatedAt`) = ?';
    if (areaId) {
      where = 'AND YEAR(`order`.`updatedAt`) = ? AND FIND_IN_SET(`house`.`areaId`, ?)';
    }
    const sql = `SELECT
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 1 THEN 1 ELSE 0 END) AS "1",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 2 THEN 1 ELSE 0 END) AS "2",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 3 THEN 1 ELSE 0 END) AS "3",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 4 THEN 1 ELSE 0 END) AS "4",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 5 THEN 1 ELSE 0 END) AS "5",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 6 THEN 1 ELSE 0 END) AS "6",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 7 THEN 1 ELSE 0 END) AS "7",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 8 THEN 1 ELSE 0 END) AS "8",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 9 THEN 1 ELSE 0 END) AS "9",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 10 THEN 1 ELSE 0 END) AS "10",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 11 THEN 1 ELSE 0 END) AS "11",
          SUM(CASE WHEN MONTH(\`order\`.\`updatedAt\`) = 12 THEN 1 ELSE 0 END) AS "12"
      FROM (SELECT \`order\`.\`updatedAt\` FROM \`order\` LEFT JOIN \`house\` ON \`order\`.\`houseId\` = \`house\`.\`id\` WHERE \`order\`.\`status\` = 2 ${where}) AS \`order\``;
    return await this.querySelect(sql, [ startTime, areaId ]);
  }
}

module.exports = OrderService;
